package com.slipper.common.excel;

import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.io.PushbackInputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelParser {
    private static final Logger logger = LoggerFactory.getLogger(ExcelParser.class);
    /**
     * 将excel文件解释成配置文件中的对象
     *
     * @param entityClass 实体类，类中包含有导入相关配置信息
     * @param inputStream 文件输入流
     * @param errorMsg 错误信息
     * @return 返回实体类对象列表
     */
    public static <T> List<T> parseExcel(
            Class<T> entityClass,
            InputStream inputStream,
            StringBuffer errorMsg) throws Exception {
        return parseExcel(entityClass, inputStream, errorMsg, null);
    }

    /**
     * 将excel文件解释成配置文件中的对象
     *
     * @param entityClass 实体类，类中包含有导入相关配置信息
     * @param inputStream 文件输入流
     * @param errorMsg 错误信息
     * @param dicData 字典数据
     * @return 返回实体类对象列表
     */
    public static <T> List<T> parseExcel(
            Class<T> entityClass,
            InputStream inputStream,
            StringBuffer errorMsg,
            Map<String, Map<String, String>> dicData) throws Exception {
        return parseExcel(entityClass, entityClass, inputStream, errorMsg, dicData);

    }

    /**
     * 将excel文件解释成配置文件中的对象
     *
     * @param entityClass 返回的实体类
     * @param excelClass 导入相关的配置信息
     * @param inputStream 文件输入流
     * @param errorMsg 错误信息
     * @param dicData 字典数据
     * @return 返回实体类对象列表
     */
    public static <T> List<T> parseExcel(
            Class<T> entityClass,
            Class<?> excelClass,
            InputStream inputStream,
            StringBuffer errorMsg,
            Map<String, Map<String, String>> dicData) throws Exception {
        ExcelInfo excelInfo = excelClass.getAnnotation(ExcelInfo.class);
        if (excelInfo == null) {
            throw new Exception(entityClass + "实体类未声名" + ExcelInfo.class + "注解信息！");
        }
        // 文件解释开始行
        int startRow = excelInfo.startRow();
        Workbook workbook;
        List<T> valueList = new ArrayList<>();
        try {
            if (!inputStream.markSupported()) {
                inputStream = new PushbackInputStream(inputStream, 8);
            }
            //workbook = new HSSFWorkbook(inputStream);
            workbook = WorkbookFactory.create(inputStream);
        } catch (Exception e) {
            throw new Exception("导入文件格式有误！");
        }
        for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
            // 获取工作溥
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            if (workbook.isSheetHidden(sheetIndex)) {
                continue;
            }
            if(sheet.getSheetName() != null && ("typelist".endsWith(sheet.getSheetName())
                    || sheet.getSheetName().startsWith("dictionary"))) {
                //这个工作薄会存有下拉框的枚举值（下拉框数据长度过大），所有忽略
                continue;
            }
            // 获取工作薄行数
            int rowCount = sheet.getPhysicalNumberOfRows();
            if (rowCount <= startRow) {
                // 工作薄行数少于应开始行数，证明此工作薄为空
                continue;
            }
            for (int rowIndex = startRow; rowIndex < rowCount; rowIndex++) {
                T entityObject = parseRow(entityClass, excelClass, sheet.getRow(rowIndex), errorMsg, dicData);
                if (entityObject != null) {
                    valueList.add(entityObject);
                }
            }
        }
        try {
            workbook.close();
        } catch (Exception e) {
            logger.error("", e);
        }
        return valueList;
    }

    /**
     * 解释excel row中的所有值并填充到实体对象中
     *
     * @param entityClass 实体类
     * @param row excel 中的某行对象
     * @return 实体对象
     */
    private static <T> T parseRow(
            Class<T> entityClass,
            Class<?> excelClass,
            Row row,
            StringBuffer errorMsg,
            Map<String, Map<String, String>> dicData) throws Exception {
        if (row == null) {
            return null;
        }

        Field[] excekFields = excelClass.getDeclaredFields();
        T entityObject = entityClass.newInstance();
        // 是否所有的属性都为空
        boolean isAllNull = true;
        boolean isSuccess = true;
        StringBuilder error = new StringBuilder();
        for (Field excelField : excekFields) {
            ExcelColumn column = excelField.getAnnotation(ExcelColumn.class);

            if (column != null) {
                Field field = entityClass.getDeclaredField(excelField.getName());
                if (column.index() < 0) { // 小于0，使用默认值
                    field.setAccessible(true);
                    field.set(entityObject, column.value());
                    continue;
                }

                // 设置对象所在的行数
                if (column.type() == ExcelColumnType.TYPE_ROW_INDEX) {
                    field.setAccessible(true);
                    field.set(entityObject, row.getRowNum() + 1);
                    continue;
                }

                Class<?> clazz = field.getType();
                Cell cell = row.getCell(column.index());
                try {
                    if (!column.nullable() && cell == null) {
                        throw new Exception("不允许为空！");
                    }
                    if (cell == null) {
                        continue;
                    }
                    // 根据实体类的属性类型，格式化单元格值
                    Object value = CellParseFactory.getInstance().getCellParse(clazz).parse(cell, column);
                    if (!column.nullable()
                            && (value == null || (value instanceof String && value.toString().isEmpty()))) {
                        throw new Exception("不允许为空！");
                    }
                    if (value != null && !value.toString().trim().isEmpty()
                            && column.dicKey() != null && !column.dicKey().isEmpty()) {
                        Map<String, String> data = dicData.get(column.dicKey());
                        if (data == null || data.isEmpty()) {
                            throw new Exception(field.getName() + "字段未配置转换字典！");
                        }

                        if (column.dicMulti()) {
                            StringBuilder builder = new StringBuilder();
                            String[] dicKeys = value.toString().split(",");
                            for (String dicKey : dicKeys) {
                                if (dicKey != null && !dicKey.isEmpty()) {
                                    String dicValue = data.get(dicKey);
                                    if (dicValue != null && !dicValue.isEmpty()) {
                                        builder.append(dicValue).append(",");
                                    }
                                }
                            }
                            if (builder.length() > 1) {
                                value = builder.toString();
                            } else {
                                value = null;
                            }
                        } else {
                            value = data.get((String) value);
                            if (value == null) {
                                throw new Exception("请输入正确的枚举内容！");
                            }
                        }
                        if (!column.nullable() && value == null) {
                            throw new Exception("请输入正确的枚举内容！");
                        }
                    }
                    if (value != null
                            && (!(value instanceof String) || !value.toString().isEmpty())) {
                        field.setAccessible(true);
                        field.set(entityObject, value);
                        isAllNull = false;
                    }
                } catch (Exception e) {
                    error.append("第 ")
                            .append(row.getRowNum() + 1)
                            .append(" 行， ")
                            .append("第 ")
                            .append(getExcelColumnIndex(column.index()))
                            .append(" 列：")
                            .append(e.getMessage())
                            .append("@@@");
                    isSuccess = false;
                }
            }
        }
        if (isAllNull) {
            // 所有值为空，证明此行为空行
            return null;
        } else {
            errorMsg.append(error);

            if (isSuccess) { // 有错误
                return entityObject;
            } else {
                return null;
            }
        }
    }

    /**
     * 根据数字索引转换成字母索引
     */
    private static String getExcelColumnIndex(int index) {
        String excelColumnIndex;
        int first = index / 26;
        if (first > 0) {
            excelColumnIndex = getExcelColumnIndex(first - 1) + (char) ('A' + index % 26);
        } else {
            excelColumnIndex = String.valueOf((char) ('A' + index));
        }
        return excelColumnIndex;
    }

    /**
     *
     * @param excelClass bean类
     * @param workbook 工作薄
     * @param dicData 字典，map中的key为中文，需要反转
     */
    public static <T> void fillDicCode(Class<T> excelClass, Workbook workbook,Map<String, Map<String, String>> dicData) throws Exception {
        ExcelInfo excelInfo = excelClass.getAnnotation(ExcelInfo.class);
        if (excelInfo == null) {
            throw new Exception(excelClass + "实体类未声名" + ExcelInfo.class + "注解信息！");
        }
        // 文件解释开始行
        int startRow = excelInfo.startRow();
        Field[] excekFields = excelClass.getDeclaredFields();

        Sheet sheet = workbook.getSheetAt(0);
        for (Field excelField : excekFields) {
            ExcelColumn column = excelField.getAnnotation(ExcelColumn.class);
            if (column != null) {
                if (!"".equals(column.dicKey())) {
                    Map<String, String> data = dicData.get(column.dicKey());
                    if (data == null || data.isEmpty()) {
                        throw new Exception(excelField.getName() + "字段未配置转换字典！");
                    }
                    String[] ptArr = new String[data.size()];
                    int i = 0;
                    for (String key : data.keySet()) {
                        ptArr[i++] = key;
                    }
                    ExcelUtil.addDataValidation(workbook, sheet, ptArr, startRow, 300, column.index(), column.index());
                }

            }
        }
    }
    
    /**
     * @param startRow    头部行
     * @param inputStream 数据
     */
    public static Map<Integer, Map<Integer, String>> parseMap(
            int startRow, InputStream inputStream) throws Exception {
        Workbook workbook;
        Map<Integer, Map<Integer, String>> resultMap = new HashMap<>();
        try {
            if (!inputStream.markSupported()) {
                inputStream = new PushbackInputStream(inputStream, 8);
            }
            workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            int rows = sheet.getPhysicalNumberOfRows();
            for (int rowIndex = startRow; rowIndex < rows; rowIndex++) {
                Map<Integer, String> dataMap = new HashMap<>();
                Row row = sheet.getRow(rowIndex);
                int cells = row.getPhysicalNumberOfCells();
                boolean isAllNull = true;
                for (int cellIndex = 0; cellIndex < cells; cellIndex++) {
                    String value = null;
                    Cell cell = row.getCell(cellIndex);
                    if (cell != null) {
                        value = cell.toString().trim();
                        dataMap.put(cellIndex, value);
                    }
                    if (value != null && !value.isEmpty()) {
                        isAllNull = false;
                    }
                }
                if (!isAllNull) {
                    resultMap.put(rowIndex, dataMap);
                }
            }
        } catch (Exception e) {
            throw new Exception("导入文件格式有误！", e);
        }
        return resultMap;
    }

}
